//Pull data for panel of individuals

clear all
odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, tjomf from dbo.Lonestatistik_2014") dsn("[Project]") clear

gsort LopNr_PersonNr LopNr_PeOrgNr - tjomf 
by LopNr_PersonNr LopNr_PeOrgNr: egen tjomf_tot=total(tjomf), missing
duplicates drop LopNr_PersonNr LopNr_PeOrgNr, force
gen year = 2014

save "[Folder]\AppAddLonStat.dta", replace



forvalues i=2008/2013 {
odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, tjomf from dbo.Lonestatistik_`i'") dsn("[Project]") clear

gsort LopNr_PersonNr LopNr_PeOrgNr - tjomf 
by LopNr_PersonNr LopNr_PeOrgNr: egen tjomf_tot=total(tjomf), missing
duplicates drop LopNr_PersonNr LopNr_PeOrgNr, force
gen year = `i'

duplicates drop LopNr_PersonNr LopNr_PeOrgNr, force

append using "[Folder]\AppAddLonStat.dta"

save  "[Folder]\AppAddLonStat.dta", replace

}


odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, Kon, Fodelsear, Sun2000niva, KU1Ssyk3_2012, KU1Ssyk4_2012, KU1Ink, ForvInk, Kommun, SjukSum_Belopp, SjukRe,  SektorKod, SenInvAr from dbo.LISA2014_Individ") dsn("[Project]") clear

rename KU1Ssyk3_2012 KU1Ssyk3
replace KU1Ssyk3 = "." if KU1Ssyk3=="***"
rename KU1Ssyk4_2012 KU1Ssyk4
replace KU1Ssyk4 = "." if KU1Ssyk4=="***"
replace KU1Ssyk4 ="." if KU1Ssyk4 =="****"

destring, replace
duplicates drop LopNr_PersonNr, force
gen year = 2014

save "[Folder]\Panel_Ind.dta", replace

		
forvalues i=2008/2013 {

odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, Kon, Fodelsear, Sun2000niva, KU1Ssyk3, KU1Ssyk4, KU1Ink, ForvInk, Kommun, SjukSum_Belopp, SjukRe, SektorKod, SenInvAr from dbo.LISA`i'_Individ") dsn("[Project]") clear


replace KU1Ssyk3 = "." if KU1Ssyk3=="***"
replace KU1Ssyk4 = "." if KU1Ssyk4=="***"
replace KU1Ssyk4 ="." if KU1Ssyk4 =="****"

destring, replace
duplicates drop LopNr_PersonNr, force
gen year = `i'

append using "[Folder]\Panel_Ind.dta"

save "[Folder]\Panel_Ind.dta", replace

}



